
import java.sql.*;   // methods to process SQL commands

import javax.sql.*;  // ditto
//import org.postgresql.jdbc3.Jdbc3PoolingDataSource; // connection via JDBC to postgrSQL
import java.io.IOException;   // enable IOException objects & handling
import java.io.PrintWriter;   // enable PrintWriter (so can use .println())
import javax.servlet.http.*;  // provides servlet functionality
import javax.servlet.*;	      // ditto
import javax.*;


public class FabflixsTest extends HttpServlet
{
	private static DataSource ds;	// contains DB and connection data; see init()
		
	public static String issueQuery()
    {	
		StringBuffer buf = new StringBuffer();	// StringBuffer is faster than String
		// see the API doc at java.sun.com for details
		try
		{
			// connect to DB
			Class.forName("org.postgresql.Driver").newInstance();
			Connection connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost/fabflixs", "testuser", "testpass");
			
			// get and build string of test DB's metadata
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery("Select * from genres_in_movies");
			buf.append("The results of the query <br/>");
			ResultSetMetaData metadata = result.getMetaData();
			System.out.println("There are " + metadata.getColumnCount() + " columns");
			for (int i = 1; i <= metadata.getColumnCount(); i++)
			buf.append("Type of column "+ i +" is " + metadata.getColumnTypeName(i) + "<br/>");
			
			// add table's contents to buffered string
			while (result.next())
			{
				buf.append("Gnere Id = " + result.getInt(1));
				buf.append(" Movie Id = " + result.getInt(2));
				buf.append(" Pair Id = " + result.getInt(3));
				buf.append("<br/>");
			}
			
			// query sent; close connection to DB
			connection.close();
		}
		catch (SQLException sqle)
		{
			buf.append("SQL exception occured");
		}
		catch (ClassNotFoundException cnfe)
		{
			buf.append("ClassNotFound exception occured");
		}
		catch (InstantiationException ie)
		{
			buf.append("Instantiation exception occured");
		}
		catch (IllegalAccessException iae)
		{
			buf.append("IllegalAccess exception occured");
		}
		
		// return buffered string of text
		return buf.toString();
	}
	
	// Print HTML for Web page heading
	public void doGet(HttpServletRequest request, HttpServletResponse response)
		throws IOException, ServletException
	{
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		
		
		
		out.println("<html>");
		out.println("<head>");
		
		out.println("<title>" + "Test of Tomcat communication with the postgreSQL test database"+"</title>");
		out.println("</head>");
		out.println("<body bgcolor=\"white\">");
		out.println("<h1>" + "Test of Tomcat communication with the postgreSQL test database" + "</h1>");
		
		out.println("<br> Test value: " + request.getParameter("test") + "<br>");
		
		out.println(issueQuery());
		
		out.println("</body>");
		out.println("</html>");
	}
	
	// Upon init of servlet, open the moviedb database
	public void init() throws ServletException
	{
		/*
		Jdbc3PoolingDataSource source = new Jdbc3PoolingDataSource();
		source.setDataSourceName("Tomcat Talking to PostgreSQL");
		source.setServerName("localhost"); // server name
		source.setDatabaseName("moviedb"); // db name
		source.setUser("testuser");        // user name
		source.setPassword("testpass");    // password :-)
		source.setMaxConnections(10);
		ds = source;
		*/
	}

	
}
